﻿using Bouyei.DbFactory.DbSqlProvider.SqlFunctions;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Linq;
using Bouyei.DbFactory.DbSqlProvider.SqlKeywords;
using System.Data;
using Bouyei.DbFactory.DbSqlProvider;

namespace Bouyei.DbFactory
{
    public static class AdoProviderExtensions
    {
        public static DbResult<List<T>, string> Query<T>(this IAdoProvider dbProvider,
            Expression<Func<T, bool>> whereClause) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Select<T>().From<T>().Where<T>(whereClause).SqlString;

            var rt = dbProvider.Query<T>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r"+ commandText;

            return rt;
        }

        public static DbResult<List<Dictionary<string,object>>, string> QueryToDictionary<T>(this IAdoProvider dbProvider,
           Func<T, dynamic> selector, Expression<Func<T, bool>> whereClause) where T:class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Select(selector).From<T>().Where<T>(whereClause).SqlString;

            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>(32);
         
            var rt = dbProvider.Query(new Parameter(commandText), (Dictionary<string,object> dict) =>
            {
                list.Add(dict);
                return true;
            });
            if (!rt.IsSuccess())
            {
                return DbResult<List<Dictionary<string, object>>, string>.Error(rt.Info + "\n\r" + commandText);
            }
  
            return DbResult<List<Dictionary<string, object>>, string>.Success(list);
        }

        public static DbResult<List<T>, string> Query<T>(this IAdoProvider dbProvider,
        string[] selectColumns, Expression<Func<T, bool>> whereClause) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            var commandText = sql.Select(selectColumns).From<T>().Where(whereClause).SqlString;
            var rt = dbProvider.Query<T>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<List<T>, string> QueryJoin<T,L,R>(this IAdoProvider dbProvider,
            Expression<Func<L, bool>> left, Expression<Func<R, bool>> rigth,
            Expression<Func<L,R, bool>> onWhere,JoinType joinType=JoinType.Left) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            string commandText = sql.Select<T>().Join<T, L, R>(left, rigth, onWhere,joinType).SqlString;

            var rt = dbProvider.Query<T>(new Parameter(commandText));
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<List<T>, string> QueryJoin<T, L, R>(this IAdoProvider dbProvider,
            string[] selectColumns,
            Expression<Func<L, bool>> left, Expression<Func<R, bool>> rigth,
            Expression<Func<L, R, bool>> onWhere, JoinType joinType = JoinType.Left) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            string commandText = sql.Select(selectColumns).Join<T, L, R>(left, rigth, onWhere, joinType).SqlString;

            var rt = dbProvider.Query<T>(new Parameter(commandText));
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<List<T>, string> Query<T>(this IAdoProvider dbProvider,
        string[] selectColumns, string tableName, Expression<Func<T, bool>> whereClause) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            var commandText = sql.Select(selectColumns).From(tableName).Where(whereClause).SqlString;
            var rt = dbProvider.Query<T>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<DataTable, string> Query<T>(this IAdoProvider dbProvider,
        Func<T,dynamic>selector, Expression<Func<T, bool>> whereClause) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            var commandText = sql.Select(selector).From<T>().Where(whereClause).SqlString;
            var rt = dbProvider.Query(new Parameter(commandText));
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<List<T>, string>QueryPage<T>(this IAdoProvider dbProvider,
           Expression<Func<T, bool>> whereClause, int page = 0, int size = 1) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            int offset = page * size;

            string commandText =  sql.Select<T>().From<T>()
                .Where<T>(whereClause).Top<T>(dbProvider.FactoryType, offset, size).SqlString;
 
            var rt = dbProvider.Query<T>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<DataTable, string> QueryPage<T>(this IAdoProvider dbProvider,
            Func<T,dynamic> selector,Expression<Func<T, bool>> whereClause, int page = 0, int size = 1) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            int offset = page * size;

            string commandText = sql.Select(selector).From<T>()
                .Where<T>(whereClause).Top<T>(dbProvider.FactoryType, offset, size).SqlString;

            var rt = dbProvider.Query(new Parameter(commandText));
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<List<Dictionary<string,object>>, string> QueryOrderByToDictonary<T>(this IAdoProvider dbProvider,
        Func<T, dynamic> selector, Expression<Func<T, bool>> whereClause,
        string[] orderColumnNames, SortType sType = SortType.Desc, int page = 0, int size = 1) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            int offset = page * size;

            string commandText = sql.Select<T>().From<T>()
                        .Where<T>(whereClause).OrderBy(sType, orderColumnNames).Top<T>(dbProvider.FactoryType, offset, size).SqlString;

            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>(size);

            var rt = dbProvider.Query(new Parameter(commandText), (Dictionary<string, object> dict) =>
            {
                list.Add(dict);
                return true;
            });
            if (!rt.IsSuccess())
            {
                return DbResult<List<Dictionary<string, object>>, string>.Error(rt.Info + "\n\r" + commandText);
            }

            return DbResult<List<Dictionary<string, object>>, string>.Success(list);
        }

        public static DbResult<List<T>, string> QueryOrderBy<T>(this IAdoProvider dbProvider,
        Expression<Func<T, bool>> whereClause,string[] orderColumnNames,
        SortType sType=SortType.Desc, int page = 0, int size = 1) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            int offset = page * size;

            string commandText = sql.Select<T>().From<T>()
                .Where<T>(whereClause).OrderBy(sType,orderColumnNames).Top<T>(dbProvider.FactoryType, offset, size).SqlString;

            var rt = dbProvider.Query<T>(new Parameter(commandText),size);
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<DataTable, string> QueryOrderBy<T>(this IAdoProvider dbProvider,
        Func<T,dynamic> selector,  Expression<Func<T, bool>> whereClause,
        string[] orderColumnNames,SortType sType = SortType.Desc, int page = 0, int size = 1) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            int offset = page * size;

            string commandText = sql.Select(selector).From<T>()
                .Where<T>(whereClause).OrderBy(sType, orderColumnNames).Top<T>(dbProvider.FactoryType, offset, size).SqlString;

            var rt = dbProvider.Query(new Parameter(commandText));
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<int,string> QueryCount<T>(this IAdoProvider dbProvider,
           Expression<Func<T, bool>> whereClause, string countColumn = "*") where T:class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            string commandText = sql.Select<T>(new Count(countColumn)).From<T>().Where(whereClause).SqlString;

           var rt= dbProvider.ExecuteScalar<int>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;
             
            return rt;
        }

        public static DbResult<R, string> QueryMax<T,R>(this IAdoProvider dbProvider,
        Expression<Func<T, bool>> whereClause, string maxColumn) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            string commandText = sql.Select<T>(new Max(maxColumn)).From<T>().Where(whereClause).SqlString;

            var rt = dbProvider.ExecuteScalar<R>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<R, string> QuerySum<T,R>(this IAdoProvider dbProvider,
        Expression<Func<T, bool>> whereClause, string sumColumn) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            string commandText = sql.Select<T>(new Sum(sumColumn)).From<T>().Where(whereClause).SqlString;

            var rt = dbProvider.ExecuteScalar<R>(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<List<Dictionary<string, object>>, string> QueryGroupBy<T>(this IAdoProvider dbProvider,
      Expression<Func<T, bool>> whereClause, Func<T, dynamic> groupColumnsSelector) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            string commandText = sql.Select(groupColumnsSelector).From<T>()
                .Where(whereClause).GroupBy(groupColumnsSelector).SqlString;

            var rt = dbProvider.Query(new Parameter(commandText), 32);
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        //sqlFunctions:new Avg("age"),new Count("*"),Max("level")
        public static DbResult<List<Dictionary<string, object>>, string> QueryGroupBy<T>(this IAdoProvider dbProvider,
       Expression<Func<T, bool>> whereClause, Func<T, dynamic> groupColumnsSelector, params IFunction[] sqlFunctions) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            string commandText = sql.Select<T>(groupColumnsSelector, sqlFunctions).From<T>()
                .Where(whereClause).GroupBy(groupColumnsSelector).SqlString;

            var rt = dbProvider.Query(new Parameter(commandText), 32);
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<List<Dictionary<string, object>>, string> QueryGroupBy<T>(this IAdoProvider dbProvider,
        Expression<Func<T, bool>> whereClause, string[] groupColumns) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);

            string commandText = sql.Select(groupColumns).From<T>()
                .Where(whereClause).GroupBy(groupColumns).SqlString;

            var rt = dbProvider.Query(new Parameter(commandText), 32);
            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<int, string> Delete<T>(this IAdoProvider dbProvider
            , Expression<Func<T, bool>> whereClause) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Delete().From<T>().Where<T>(whereClause).SqlString;

            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));
            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<int, string> Update<T>(this IAdoProvider dbProvider,
            T value, Expression<Func<T, bool>> whereClause,
             MappingOption option = MappingOption.None) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Update<T>().Set<T>(value,option).Where<T>(whereClause).SqlString;

            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));

            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<int, string> Update<T>(this IAdoProvider dbProvider,
       Dictionary<string, object> setKeyValues, Expression<Func<T, bool>> whereClause) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Update<T>().Set(setKeyValues).Where(whereClause).SqlString;

            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));

            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<int, string> Update<T>(this IAdoProvider dbProvider,
        Func<T, dynamic> selector, Expression<Func<T, bool>> whereClause,
        MappingOption option= MappingOption.None) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Update<T>().Set(selector,option).Where(whereClause).SqlString;

            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));

            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }

        public static DbResult<int, string> Insert<T>(this IAdoProvider dbProvider,
            Func<T,dynamic>selector, MappingOption option = MappingOption.None) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Insert<T>().Values(selector,option).SqlString;
            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));

            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        /// <summary>
        /// 多个对象并且忽略空值则需要注意每一项空值一致，因为以第一项未默认列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbProvider"></param>
        /// <param name="option"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DbResult<int, string> Insert<T>(this IAdoProvider dbProvider,
            MappingOption option = MappingOption.None, params T[] values) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Insert<T>().Values<T>(option, values).SqlString;
            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));

            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<int, string> InsertParameter<T>(this IAdoProvider dbProvider, T value) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Insert<T>().SqlString;

            var wb = new WordsBase(typeof(T), AttributeType.IgnoreWrite);
            var pros = wb.GetProperties().ToArray();

            string valueStr = string.Empty;
            string flag = "@";

            if (dbProvider.FactoryType == FactoryType.Oracle)
            {
                flag = ":";
            }
            else if (dbProvider.FactoryType == FactoryType.MySql)
            {
                flag = "?";
            }
            else if(dbProvider.FactoryType== FactoryType.DB2)
            {
                flag = "";
            }

            valueStr=string.Join(",", pros.Select(x => flag+ x.Name));
            commandText = commandText + " Values(" + valueStr + ")";

            int cnt = pros.Length;
            var param = new Parameter(commandText);
            param.Columns = new CmdParameter[cnt];
            for (int i = 0; i < cnt; ++i)
            {
                var pro = pros[i];
                var v = pro.GetValue(value, null);

                param.Columns[i] = new CmdParameter()
                {
                    DbType = (DbType)Enum.Parse(typeof(DbType), pro.PropertyType.Name),
                    Value = (v ?? DBNull.Value),
                    ParameterName = flag + pro.Name
                };
            }

            var rt = dbProvider.ExecuteCmd(param);

            if (rt.IsSuccess() == false)
                rt.Info = rt.Info + "\n\r" + commandText;

            return rt;
        }

        public static DbResult<int, string> Insert<T>(this IAdoProvider dbProvider,
           Dictionary<string, object> insertKeyValues) where T : class
        {
            ISqlProvider sql = SqlProvider.CreateProvider(dbProvider.FactoryType);
            var commandText = sql.Insert<T>().Values(insertKeyValues).SqlString;

            var rt = dbProvider.ExecuteCmd(new Parameter(commandText));

            if (rt.IsSuccess()==false)
                rt.Info = rt.Info + "\r\n" + commandText;

            return rt;
        }
    }
}
